package com.meeting.y.model;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.slf4j.LoggerFactory;

import java.util.List;

public class PaperDao {

    public static final PaperDao dao = new PaperDao();

    public List<Record> getUserPagerCount(Integer userid,Integer surveyid,Integer answerrecordid){

        List<Record> list = Db.find("select '总分' columntype,'总分' columnname,count(1) count\n" +
                "from answer a,`option` b,answer_record c\n" +
                "where b.optionid = a.optionid  and c.id = a.answer_record_id\n" +
                "   and b.optioncontent = '是' and a.userid = ? and a.answer_record_id = ? and c.surveyid = ? ",userid,answerrecordid,surveyid);

        List<Record> list1 = Db.find("select aa.columntype,bb.meeting_enum_showname columnname,count(1) count from (\n" +
                "    /*这里是每个人多次答题 算一分，应该按次数计算才对*/\n" +
                "select c.quesid,c.quescontent,c.columntype,b.optionid,b.optioncontent" +
                "      from answer a,`option` b,ques c \n" +
                "where a.userid = ? and b.optionid = a.optionid and a.answer_record_id = ?\n" +
                "  and b.quesid = c.quesid and b.optioncontent = '是' and c.surveyid = ?\n" +
                "              ) aa left join meeting_enum bb on aa.columntype = bb.meeting_enum_val and bb.meeting_enum_key ='quescolumns'\n" +
                "group by bb.meeting_enum_showname,aa.columntype",userid,answerrecordid,surveyid);
        list.addAll(list1);
        return list;
    }

    public List<Record> getUserPagerAgeCount(Integer userid,Integer surveyid,Integer answerrecordid){

        List<Record> list = Db.find("select '总分' columntype,'总分' columnname,count(1) count\n" +
                "from answer a,`option` b,answer_record c\n" +
                "where b.optionid = a.optionid  and c.id = a.answer_record_id\n" +
                "   and b.optioncontent = '是' and a.userid = ? and a.answer_record_id = ? and c.surveyid = ? ",userid,answerrecordid,surveyid);

        List<Record> list2 = Db.find("select c.monthage columntype,c.monthage columnname,count(1) count from(\n" +
                "select c.monthage,c.quesid,c.quescontent,c.columntype,b.optionid,b.optioncontent from answer a,`option` b,ques c, answer_record e\n" +
                "where b.optionid = a.optionid  and a.answer_record_id = e.id\n" +
                "    and b.quesid = c.quesid and b.optioncontent = '是' and a.userid = ? and a.answer_record_id = ? and c.surveyid = ? \n" +
                " )c\n" +
                "group by c.monthage order by SUBSTRING_INDEX(c.monthage,'-',1)+0",userid,answerrecordid,surveyid);
        list.addAll(list2);
        return list;
    }

    public List<Record> getAgeColumnsCount(Integer surveyid,Integer answerrecordid){
        List<Record> list2 = Db.find("select c.monthage monthage,(select meeting_enum_showname from\n" +
                "      meeting_enum where meeting_enum_val = c.columntype and meeting_enum_key = 'quescolumns') columnname,c.columntype,count(1) count from(\n" +
                "                select c.monthage,c.quesid,c.quescontent,c.columntype,\n" +
                "                       b.optionid,b.optioncontent\n" +
                "                from answer a,`option` b,ques c, answer_record e\n" +
                "                where b.optionid = a.optionid  and a.answer_record_id = e.id\n" +
                "                    and b.quesid = c.quesid and b.optioncontent = '是' \n" +//and a.userid = ?
                "                  and a.answer_record_id = ? and c.surveyid = ?\n" +
                "                 )c\n" +
                "                group by c.columntype,c.monthage  order by c.columntype,SUBSTRING_INDEX(c.monthage,'-',1)+0",answerrecordid,surveyid);
        return list2;
    }

    public List<Record> getQuesDetailByType(Integer recordid,Integer type){
        String sql = "select e.surveyid,c.quesid,c.quescontent,c.columntype,c.monthage,c.quesorder,b.optionid,\n" +
                "       if(b.optioncontent='是',1,0) as optionvalue,b.optioncontent\n" +
                "from answer a,`option` b,ques c, answer_record e\n" +
                "where b.optionid = a.optionid  and a.answer_record_id = e.id\n" +
                "  and b.quesid = c.quesid and e.surveyid = c.surveyid\n" +
                "  and a.answer_record_id = ?" ;
        if(type != null ) {
            sql+=" and c.columntype = ? ";
        }
               sql += " order by c.columntype, c.quesorder ";
        if(type != null ) {
            return Db.find(sql, recordid, type);
        }else {
            return Db.find(sql, recordid);
        }
    }

    public String getAnswerUserName(Integer answerrecordid){
        String sql = "select b.nickName from answer_record a,user b where a.userid = b.userid and a.id = ?";
        String record = Db.queryFirst(sql,answerrecordid);
        return record;
    }

    public List<Record> getQuesDetailByType_Export(Integer recordid,Integer type){
        String sql = "select c.quescontent, if(b.optioncontent='是','v','') as optionvalue\n" +
                "from answer a,`option` b,ques c, answer_record e\n" +
                "where b.optionid = a.optionid  and a.answer_record_id = e.id\n" +
                "  and b.quesid = c.quesid and e.surveyid = c.surveyid\n" +
                "  and a.answer_record_id = ?" ;
        if(type != null ) {
            sql+=" and c.columntype = ? ";
        }
        sql += " order by c.columntype, c.quesorder ";
        if(type != null ) {
            return Db.find(sql, recordid, type);
        }else {
            return Db.find(sql, recordid);
        }
    }

    public List<Record> getTypesBySurveyid(Integer surveyid){
        String sql = "select distinct b.meeting_enum_showname showname,b.meeting_enum_val val from ques a,meeting_enum b where a.columntype = b.meeting_enum_val\n" +
                "                                    and b.meeting_enum_key = 'quescolumns' and a.surveyid = ?\n" +
                "    order by b.meeting_enum_val";
        return Db.find(sql,surveyid);

    }

    public List<Record> getPaperRela(Integer surveyid){
        String appsql = "";
        if(surveyid!=null && surveyid>0){
            appsql = " and c.surveyid = "+surveyid;
        }
        return Db.find("select c.surveyid,a.quesid,a.optionid,a.optioncontent,b.answerid,b.userid from " +
                "ques c left join `option` a on c.quesid = a.quesid " +
                "left join answer b on a.optionid = b.optionid \n" +
                "where  1=1  "+appsql);
    }

    public Integer getDoingQuesOrder(Integer surveyid,Integer userid){
        Record result = Db.findFirst("select max(c.quesorder) maxquesorder\n" +
                "from ques c left join `option` a on c.quesid = a.quesid\n" +
                "            left join answer b on a.optionid = b.optionid\n" +
                "where 1=1 and c.surveyid = ? and b.userid = ?",surveyid,userid);
        Integer quesorder = result.getInt("maxquesorder");
        return quesorder;
    }

    public Record getDoingRedordOrder(Integer surveyid,Integer userid){
        Record result = Db.findFirst("select max(c.quesorder) maxquesorder,max(b.answer_record_id) maxrecordid \n" +
                "from ques c left join `option` a on c.quesid = a.quesid\n" +
                "            left join answer b on a.optionid = b.optionid\n" +
                "            left join answer_record e on b.answer_record_id = e.id "+
                "where 1=1 and c.surveyid = ? and b.userid = ? and e.status = 1",surveyid,userid);
        return result;
    }

    public int saveQues(Record record){
        Db.save("ques","quesid",record);
        return record.getInt("quesid");
    }

    public int saveOption(Record record){
        Db.save("option","optionid",record);
        return record.getInt("optionid");
    }

    public int saveUser(Record record){
        Db.save("user","userid",record);
        return record.getInt("userid");
    }

    public int deleteObjIn(String tablename,String findKey,Object[] ids){
        if(ids ==null || ids.length ==0){
            return 0;
        }
        String sql = "delete from "+tablename+ " where "+findKey +" in (";
        for(int i=0;i<ids.length;i++){
            sql += "?";
            if(i != ids.length-1){
                sql+= ",";
            }
        }
        sql+=")";
        LoggerFactory.getLogger("PaperDao-deleteSurvey"+sql+ids);
        return Db.delete(sql,ids);
    }


}
